package manager.util;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

public class JDBC {
    private static Connection con=null;
    private static Statement sta=null;
    private static ResultSet rs=null;

    static {
        try {
            //文件路径
            //项目路径
            Properties prop = new Properties();
            prop.load(JDBC.class.getClassLoader().getResourceAsStream("jdbc.properties"));

            //从文件中读取属性
            String diverClassName=prop.getProperty("driverClassName");
            String url=prop.getProperty("url");
            String username=prop.getProperty("username");
            String userpass=prop.getProperty("userpass");
            //将外部信息放到配置文件中
            Class.forName(diverClassName);
            con=DriverManager.getConnection(url, username, userpass);
            sta=con.createStatement();
        }catch(Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 创建连接对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Connection con=null;
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydata?useSSL=false&characterEncoding=utf8";
        String user = "root";
        String password = "123456";
        con=DriverManager.getConnection(url, user, password);
        return con;
    }
    /**
     * 获取执行对象
     * @param con
     * @return
     * @throws SQLException
     */
    public static Statement getStatement(Connection con) throws SQLException {
        Statement sta=null;
        sta=con.createStatement();
        return sta;
    }


    /**
     * 新增数据
     * @param sta
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int insert(Statement sta,String sql) throws SQLException {
        return sta.executeUpdate(sql);
    }
    /**
     * 新增
     * @param sql
     * @return
     */
    public static int insert(String sql) {
        int result=0;
        try {
            result=sta.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }
    /**
     * 修改数据
     * @param sta
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int update(Statement sta,String sql) throws SQLException {
        return sta.executeUpdate(sql);
    }

    public static int update(String sql) {
        int result=0;
        try {
            result=sta.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }
    /**
     * 删除数据
     * @param sta
     * @param sql
     * @return
     * @throws SQLException
     */
    public static int delete(Statement sta,String sql) throws SQLException {
        return sta.executeUpdate(sql);
    }
    public static int delete(String sql) {
        int result=0;
        try {
            result=sta.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }


    /**
     * 关闭连接
     * @param sta
     * @param con
     */
    public static void close(ResultSet rs,Statement sta,Connection con) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(sta!=null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null) {
            try {
                con.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 预编译
     */
    public static List query(String sql,Class clazz,Object... param) {
        PreparedStatement psta=null;
        ResultSet rs=null;
        List list = null;
        try {
            psta = con.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                psta.setObject(i+1,param[i]);
            }
            rs = psta.executeQuery();
            list = getdata(rs,clazz);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(rs,psta,null);
        }
        return list;
    }

    public static int insert(String sql,Object[] param){
        PreparedStatement psta=null;
        int count = 0;
        try {
            psta = con.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                psta.setObject(i + 1, param[i]);
            }
            count = psta.executeUpdate();
        }catch (Exception e){

        }finally {
            close(null,psta,null);
        }
        return count;
    }


    public static int update(String sql,Object[] param){
        PreparedStatement psta=null;
        int count = 0;
        try {
            psta = con.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                psta.setObject(i + 1, param[i]);
            }
            count = psta.executeUpdate();
            System.out.println(count+"jjjjjjdbc");
        }catch (Exception e){

        }finally {
            close(null,psta,null);
        }
        return count;
    }

    public static int delete(String sql,Object[] param){
        PreparedStatement psta=null;
        int count = 0;
        try {
            psta = con.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                psta.setObject(i + 1, param[i]);
            }
            count = psta.executeUpdate();
        }catch (Exception e){

        }finally {
            close(null,psta,null);
        }
        return count;
    }


    private static List getdata(ResultSet rs,Class clazz){
        List list = new ArrayList();
        try {
            // 获取类对象中所有的属性
            Field[] f_arr = clazz.getDeclaredFields();
            // 设置所有属性可访问
            for (Field f : f_arr) {
                f.setAccessible(true);
            }
            // 解析结果集-- 循环数据库的结果集
            while (rs.next()) {
                // 一行数据就是一个对象(Class指定的对象)
                // 实例化对象-- 执行new 无参构造方法
                Object obj = clazz.newInstance();
                for (Field f : f_arr) {
                    //data就是从数据库中取出来的和属性f对应的数据
                    Object data = null;
                    try {
                        String fname = f.getName();// 属性名
                        //获得属性对应的类的类名字符串
                        String fieldTypeName=f.getGenericType().getTypeName();
                        if (fieldTypeName.equals(String.class.getName())) {
                            data = rs.getString(fname);
                        } else if (fieldTypeName.equals(Integer.class.getName())||fieldTypeName.equals(int.class.getName())) {
                            data = rs.getInt(fname);
                        } else if (fieldTypeName.equals(double.class.getName())|| fieldTypeName.equals(Double.class.getName())) {
                            data = rs.getDouble(fname);
                        } else if (fieldTypeName.equals(BigDecimal.class.getName()) ) {
                            data = rs.getString(fname);
                            data = new BigDecimal((String) data);
                        } else if (fieldTypeName.equals(Date.class.getName())) {
                            java.sql.Date date=rs.getDate(fname);
                            Time time=rs.getTime(fname);
                            data=new Date(date.getTime()+time.getTime()+1000*60*60*8);
                        } else {
                            data = rs.getObject(fname);
                        }
                        f.set(obj, data);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                list.add(obj);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }


    public static void close() {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(sta!=null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null) {
            try {
                con.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
//
//    public static <E> List<E> query(String sql, Class<E> clazz) {
//
//    }

    public static int queryNum(String sql,Object[] param){
        PreparedStatement psta=null;
        ResultSet rs = null;
        int count = 0;
        try {
            psta = con.prepareStatement(sql);
            for (int i = 0; i < param.length; i++) {
                psta.setObject(i + 1, param[i]);
            }
            rs = psta.executeQuery();
            rs.next();
            count = rs.getInt(1);
        }catch (Exception e){

        }finally {
            close(null,psta,null);
        }
        return count;
    }

}